import sqlite3 from 'sqlite3';
const db = new sqlite3.Database('./students.db'); // 使用文件数据库

// 创建 users 表
db.run(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    name TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('admin', 'teacher', 'student')),
    email TEXT,
    phone TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// 创建 courses 表
db.run(`
  CREATE TABLE IF NOT EXISTS courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    code TEXT NOT NULL UNIQUE,
    description TEXT,
    credit INTEGER NOT NULL,
    capacity INTEGER NOT NULL,
    current_enrollment INTEGER DEFAULT 0,
    teacher_id INTEGER NOT NULL,
    location TEXT,
    schedule TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE CASCADE
  )
`);

// 创建 selections 表
db.run(`
  CREATE TABLE IF NOT EXISTS selections (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'selected' CHECK (status IN ('selected', 'dropped')),
    score REAL,
    selection_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    drop_date DATETIME,
    UNIQUE(student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
  )
`);

// 插入管理员用户
db.serialize(() => {
  db.run(
    `INSERT OR IGNORE INTO users (username, password, name, role) VALUES (?, ?, ?, ?)`,
    ['admin', 'hashed_password_here', '系统管理员', 'admin']
  );

  // 插入示例课程
db.run(
  `INSERT OR IGNORE INTO courses (name, code, description, credit, capacity, teacher_id, location, schedule) 
   VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
  ['数据结构与算法', 'CS101', '介绍常用数据结构及其实现原理', 3, 30, 1, 'A栋301', '周一 8:00-9:40']
);
});

export default db;